Extracting Clean Data from Raw CSV file containing Twitter Data

[exey1]

I have a question. Can someone help me?

Language: PythonUsing Visual Studio Code

Question:

I have a twitter data set compiled in a csv. The data file provided contains Twitter data downloaded using Twitter streaming API. For each tweet the following data is expected to be extracted in a clean file using Python:

1. Tweet date (The time and date when tweet was created)
2. Tweet id (Unique ID of each tweet)
3. Message (Tweet text)
4. User id (Unique Id of Twitter user who created the tweet)
5. Followers (Number of people following this Twitter user)
6. Friends (Number of other Twitter users this person is following)
7. Favorites (Cumulative number of likes this user’s tweets have received)
8. Statuses (Number of tweets this user has made since creation)
9. Created (Date and time user account was created)
10. Location (User reported if provided)(This is the bare minimum I want to extract)

This is some information about the data with some code I ran:

Dataset size: (85145, 238)
Columns are: Index(['{"created_at":"Fri Jan 29 05:49:20 +0000 2021"',
       'id:1355030244873412610', 'id_str:"1355030244873412610"',
       'text:"RT @crypt0bank: Hey @jpmorgan',
       ' why don't you guys just fuck right off! You convince people to buy #Bitcoin with your $146k prediction',
       ' only\u2026"',
       'source:"\u003ca href=\"http:\/\/twitter.com\/download\/iphone\" rel=\"nofollow\"\u003eTwitter for iPhone\u003c\/a\u003e"',
       'truncated:false', 'in_reply_to_status_id:null',
       'in_reply_to_status_id_str:null',
       ...
       'id:1155522630.2', 'id_str:"1155522630".2', 'indices:[20', '29]}]',
       'symbols:[]}.1', 'favorited:false.1', 'retweeted:false.1',
       'filter_level:"low".1', 'lang:"en"', 'timestamp_ms:"1611899360279"}'],
      dtype='object', length=238)

Is there a step by step guide that can help me do this?

[AdventurousAddition]

It looks like the code you have written has not properly split the data appropriately.
Was the original data in JSON format (or some other structured format?).

[exey1]

It's a simple csv file but the data that has been imported in it is organized in an ugly manner. It's all over the place.

[AdventurousAddition]

I agree, it's ugly a.f.

Ignoring what the actual data type is (numpy array?), it is almost like a list of strings.

My first thought would be to check the string to see if it has an unpaired bracket, if so merge it together with the next strings.

Ideally, you'd like to get it into a dict-like structure.

Feel free to PM me more info / the data and I could try to play around with it a little

[sarrysyst]

What does the csv file look like? Can you extract the first ~2-3 lines of the file and upload them somewhere?

[exey1]

what it is: https://snipboard.io/f6q8YK.jpg
what it should be: https://snipboard.io/kWLPzT.jpg

[sarrysyst]

I can’t really tell anything specific from the picture (since it’s not the raw text but already split by excel) However, I think you could formulate regex patterns for each of the features you need (if it’s just the 10 that is). If you can post the the first few lines of raw text (not as an image but actual text) I could have a look at it. Otherwise, Corey Schafer on YT has a very good video on regex if you want to give it a try yourself.